package com.cloudpivot.receivales.excel.repository;

import com.cloudpivot.receivales.excel.entity.SalePlanEntity;
import com.cloudpivot.receivales.excel.entity.SalePlanPersonEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 计算跟进数据的Repository接口
 */
@Repository
public interface SalePlanCalRepository extends JpaRepository<SalePlanEntity,String> {

    /**
     * 找到全部的跟进信息
     */
    @Query(nativeQuery = true,value =  "SELECT i5zdm_key_initiatives_sheet.id AS id, " +
            "    SUBSTR(get_user_dept_path_fn(d.id), LOCATE('/', get_user_dept_path_fn(d.id)) + 1) AS dept, " +
            "    d.name AS creater, " +
            "    DATE_FORMAT(i5zdm_follownote.createdTime, '%Y-%m-%d') AS createTime, " +
            "    DATE_FORMAT(i5zdm_follownote.followTime, '%Y-%m-%d') AS followTime, " +
            "    i5zdm_key_initiatives_sheet.target_type AS targetType, " +
            "    i5zdm_key_initiatives_sheet.project_name AS projectName, " +
            "    i5zdm_key_initiatives_sheet.key_initiative AS keyInitiative " +
            "FROM " +
            "    i5zdm_follownote " +
            "    INNER JOIN i5zdm_key_initiatives_sheet  " +
            "        ON i5zdm_follownote.id = i5zdm_key_initiatives_sheet.parentId " +
            "    LEFT JOIN h_org_user d " +
            "        ON i5zdm_follownote.creater = d.id " +
            "WHERE " +
            "    i5zdm_follownote.createdTime > '2025-10-10' and i5zdm_follownote.sequenceStatus='COMPLETED' " +
            "ORDER BY " +
            "    i5zdm_key_initiatives_sheet.project_name, " +
            "    i5zdm_follownote.createdTime")
    List<SalePlanEntity> getAllEntity();


}
